IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateAnimalProtocolSubmissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateAnimalProtocolSubmissions]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************
* Name:			UpdateAnimalProtocolSubmissions
* Purpose:		Update ProtocolSubmission with any updates from Rex, f
***************************************************************************/
CREATE PROCEDURE dbo.UpdateAnimalProtocolSubmissions
AS
BEGIN

	SET NOCOUNT ON

		BEGIN TRY
		BEGIN TRANSACTION UpdateAnimalProtocolSubs

		-- Set ProtocolSubmissionId for submissions that exist in Insight
		UPDATE Stg 
		SET Stg.ProtocolSubmissionId = PS.ID		
		FROM Stg_AnimalProtocolSubmission Stg JOIN ProtocolSubmission PS
			ON Stg.EIRBProtocolId = PS.ProtocolId
			AND Stg.FormsID = PS.FormsID
		WHERE  PS.ModuleType = 'A'
			AND Stg.EIRBProtocolId IS NOT NULL
			AND SubmissionToBeInserted  = 0

		Update ProtocolSubmission Set SubmissionDt = Stg.SubmissionDt
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.SubmissionDt, '') != ISNULL(Stg.SubmissionDt, '')
				AND Stg.SubmissionDt IS NOT NULL
				AND Stg.SubmissionToBeInserted = 0
				AND Stg.ProtocolSubmissionId IS NOT NULL

		
		Update ProtocolSubmission Set DateLastModified = Stg.DateLastModified
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.DateLastModified, '') != ISNULL(Stg.DateLastModified, '')
				AND Stg.DateLastModified IS NOT NULL
				AND Stg.SubmissionToBeInserted = 0
				AND Stg.ProtocolSubmissionId IS NOT NULL

		Update ProtocolSubmission Set DateReceived = Stg.DateReceived
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.DateReceived, '') != ISNULL(Stg.DateReceived, '')
				AND Stg.DateReceived IS NOT NULL
				AND Stg.SubmissionToBeInserted = 0
				AND Stg.ProtocolSubmissionId IS NOT NULL

		Update ProtocolSubmission Set SubmissionProjectStatusID = Stg.SubmissionProjStatusID
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.SubmissionProjectStatusID, 0) != ISNULL(Stg.SubmissionProjStatusID, 0)
				AND Stg.SubmissionProjStatusID IS NOT NULL
				AND Stg.SubmissionToBeInserted = 0
				AND Stg.ProtocolSubmissionId IS NOT NULL

		Update ProtocolSubmission Set RexIRBStatusId = Stg.PeriodId
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.RexIRBStatusId, 0) != ISNULL(PeriodId, 0)
				AND Stg.SubmissionToBeInserted = 0
				AND Stg.ProtocolSubmissionId IS NOT NULL
				AND ProtocolSubmission.ModuleType = 'A'

		UPDATE ProtocolSubmission SET ProtocolProcessId = Stg.ProtocolProcessId
			FROM Stg_AnimalProtocolSubmission Stg
			Where ProtocolSubmission.Id = Stg.ProtocolSubmissionId AND
				ISNULL(ProtocolSubmission.ProtocolProcessId, 0) != ISNULL(Stg.ProtocolProcessId, 0)
				AND Stg.ProtocolProcessID != 0
				AND Stg.ProtocolProcessID IS NOT NULL
				AND Stg.ProtocolSubmissionId IS NOT NULL
				AND ProtocolSubmission.ModuleType = 'A'
		
		
	----------------------------------------------------------------------
	-- COMMIT THE TRANSACTION!!!
	-- And end the try block, then handle any errors.
	----------------------------------------------------------------------
	COMMIT TRANSACTION UpdateAnimalProtocolSubs
	END TRY

	BEGIN CATCH
		DECLARE 
			@ErrorMessage    NVARCHAR(4000),
			@ErrorNumber     INT,
			@ErrorSeverity   INT,
			@ErrorState      INT,
			@ErrorLine       INT,
			@ErrorProcedure  NVARCHAR(200);

		-- Assign variables to error-handling functions that 
		-- capture information for RAISERROR.
		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

		-- Building the message string that will contain original
		-- error information.
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();
	
		-----------------------------------------------------
		-- ROLLBACK the transaction if we got here....
		-----------------------------------------------------
		IF XACT_STATE() <> 0 Rollback TRANSACTION UpdateAnimalProtocolSubs

		-- Raise an error: msg_str parameter of RAISERROR will contain
		-- the original error information.
		RAISERROR 
			(
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
			);
			return 99
		END CATCH;

	END
		
	SET NOCOUNT OFF



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
